CREATE DEFINER=`computraceqa`@`%` PROCEDURE `computraceqa`.`GetEmployeeWorkingTime`(
    IN p_emp_code VARCHAR(255),
    IN p_work_logged_on TIMESTAMP,
    IN p_work_ended_on TIMESTAMP,
    IN p_page INT,
    IN p_page_size INT,
    IN p_ignore_paging BOOLEAN,
    IN p_sort_column VARCHAR(50),  -- Sort column
    IN p_sort_type VARCHAR(4)      -- Sort type ('ASC' or 'DESC')
)
BEGIN
    DECLARE v_offset INT;
    DECLARE v_total_records INT;
    DECLARE v_total_pages INT;
    DECLARE v_sort_query VARCHAR(255);
    DECLARE v_limit_query VARCHAR(100);

    -- Set default sort column and sort type if not provided
    IF p_sort_column IS NULL OR p_sort_column = '' THEN
        SET p_sort_column = 'work_logged_on';
    ELSEIF p_sort_column = 'EmpCode' THEN
        SET p_sort_column = 'emp_code';  -- Map "EmpCode" to the correct column name "emp_code"
    ELSEIF p_sort_column = 'Id' THEN
        SET p_sort_column = 'id';  -- Map "Id" to the correct column name "id"
    ELSEIF p_sort_column = 'TotalActive' THEN
        SET p_sort_column = 'total_active';  -- Map "TotalActive" to the correct column name "total_active"
    ELSEIF p_sort_column = 'TotalIdle' THEN
        SET p_sort_column = 'total_idle';  -- Map "TotalIdle" to the correct column name "total_idle"
    ELSEIF p_sort_column = 'WorkLoggedOn' THEN
        SET p_sort_column = 'work_logged_on';  -- Map "WorkLoggedOn" to the correct column name "work_logged_on"
    ELSEIF p_sort_column = 'CreatedOn' THEN
        SET p_sort_column = 'created_on';  -- Map "CreatedOn" to the correct column name "created_on"
    ELSEIF p_sort_column = 'UpdatedOn' THEN
        SET p_sort_column = 'updated_on';  -- Map "UpdatedOn" to the correct column name "updated_on"
    ELSEIF p_sort_column = 'TotalSleep' THEN
        SET p_sort_column = 'total_sleep';  -- Map "TotalSleep" to the correct column name "total_sleep"
    END IF;

    IF p_sort_type IS NULL OR (p_sort_type != 'ASC' AND p_sort_type != 'DESC') THEN
        SET p_sort_type = 'DESC';
    END IF;

    -- Build the ORDER BY clause dynamically
    SET v_sort_query = CONCAT(' ORDER BY ', p_sort_column, ' ', p_sort_type);

    -- Calculate the offset for pagination if paging is not ignored
    IF NOT p_ignore_paging THEN
        SET v_offset = (p_page - 1) * p_page_size;
        SET v_limit_query = CONCAT(' LIMIT ', v_offset, ', ', p_page_size);
    ELSE
        SET v_limit_query = ''; -- No LIMIT clause if paging is ignored
    END IF;

    -- Calculate the total number of records
    SET @query = CONCAT('SELECT COUNT(*) INTO @v_total_records FROM employee_working_time WHERE 1 = 1 ',
                        IF(p_emp_code IS NOT NULL, CONCAT('AND lower(emp_code) LIKE "%', p_emp_code, '%" '), ''),
                        IF(p_work_logged_on IS NOT NULL, CONCAT('AND DATE(work_logged_on) >= DATE("', p_work_logged_on, '") '), ''),
                        IF(p_work_ended_on IS NOT NULL, CONCAT('AND DATE(work_logged_on) <= DATE("', p_work_ended_on, '") '), ''));
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Retrieve the count result
    SELECT @v_total_records INTO v_total_records;
    SET v_total_pages = CEIL(v_total_records / p_page_size);

    -- Select the records with pagination if paging is not ignored
    SET @query = CONCAT('SELECT id, emp_code, total_active, total_idle, work_logged_on, created_on, updated_on, total_sleep, ',
                        v_total_records, ' AS total_records, ', 
                        v_total_pages, ' AS total_pages FROM employee_working_time WHERE 1 = 1 ',
                        IF(p_emp_code IS NOT NULL, CONCAT('AND lower(emp_code) LIKE "%', p_emp_code, '%" '), ''),
                        IF(p_work_logged_on IS NOT NULL, CONCAT('AND DATE(work_logged_on) >= DATE("', p_work_logged_on, '") '), ''),
                        IF(p_work_ended_on IS NOT NULL, CONCAT('AND DATE(work_logged_on) <= DATE("', p_work_ended_on, '") '), ''),
                        v_sort_query, 
                        v_limit_query);

    -- Execute the dynamically built query
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END